package com.ts.file;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ts.comm.SysNumber;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

/**
 * Created by majunpeng on 2017/3/21.
 * 操作excel的工具类
 */
public class ExcelUtil {

    /**
     * excel导出
     * @param code json串
     * @return
     */

    public static HSSFWorkbook exportExcel(String code){

        int rowNumber;                                              //记录目前操作第几行
        int columnNumber;                                           //表格列数
        String title;                                               //表格title
        String footer;                                              //表格的footer
        List<List<Map>> headerList  = new ArrayList<List<Map>>();          //子标题的list
        List<String> fieldList      = new ArrayList<String>();          //field的list
        List<String> fieldNameList  = new ArrayList<String>();          //标题的list
        List<String> textAlignList  = new ArrayList<String>();          //标题对齐方式的list
        List<JSONObject> rowsList   = new ArrayList<JSONObject>();          //数据的list
        List<Integer> colspanList   = new ArrayList<Integer>();     //colspan 的list
        List<Integer> widthList     = new ArrayList<Integer>();     //width的list

        /**
         * 获取数据
         * title:标题
         * footer:excel尾部
         * headerArray:子标题
         * headerList:表头栏
         * rowsArray:表格数据
         */
        JSONObject configObject             = JSONObject.parseObject(code);
        title                               = configObject.getString("title");
        footer                              = configObject.getString("footer");
        JSONArray headerDataArray           = configObject.getJSONArray("headerData");
        JSONArray columnArray               = configObject.getJSONArray("columns");
        JSONArray rowsArray                 = configObject.getJSONArray("rows");
//        headerList                          = headerArray.toJavaList(String.class);
        rowsList                            = rowsArray.toJavaList(JSONObject.class);
        List<JSONObject> columnList         = columnArray.toJavaList(JSONObject.class);
        List<JSONArray>  headerDataList     = headerDataArray.toJavaList(JSONArray.class);


        /*初始化数据*/
        rowNumber    = 0;
        columnNumber = columnList.size();
        for(JSONObject column:columnList){
            fieldList.add(column.getString("field"));
            fieldNameList.add(column.getString("title"));
            textAlignList.add(column.getString("align"));
            colspanList.add(column.getInteger("colspan"));
            widthList.add(column.getInteger("width"));
        }

        for(JSONArray headArray:headerDataList){
            List<JSONObject> list = headArray.toJavaList(JSONObject.class);
            List<Map> resultHeaderList = new ArrayList<Map>() ;
            for(JSONObject header:list){
                Map<String,Object> map = new HashMap<String,Object>();
                if(header.getString("field") == "state"){
                    map.put("title","选中");
                }else{
                    map.put("title",header.getString("title"));
                }

                if(header.getInteger("colspan")!= null){
                    map.put("colspan",header.getInteger("colspan"));
                }else{
                    map.put("colspan",1);
                }

                if(header.getInteger("rowspan") != null){
                    map.put("rowspan",header.getInteger("rowspan"));
                }else{
                    map.put("rowspan",1);
                }

                map.put("align",header.getString("align"));
                map.put("halign",header.getString("halign"));
                map.put("valign",header.getString("valign"));
                resultHeaderList.add(map);
            }

            headerList.add(resultHeaderList);

        }

        int arrayWidth = 0 ;
        for(JSONObject column:columnList){
            arrayWidth += column.getInteger("colspan");
        }
        int arrayHeight = headerDataList.size();
        boolean flag[][] = new boolean[arrayHeight][arrayWidth];

        for(int i = 0 ; i < arrayHeight; i ++){
            for(int j = 0 ; j < arrayWidth ; j++)
                flag[i][j] = true;
        }

        /*新建工作簿:*/
        HSSFWorkbook wb     = new HSSFWorkbook();
        /*建立新的sheet对象:*/
        HSSFSheet sheet     = wb.createSheet(title);

        HSSFCellStyle cellStyle = wb.createCellStyle();

        for(int i = 0 ; i < headerList.size() ;  i++ ){
            HSSFRow row = sheet.createRow(rowNumber);

            List<Map> columnlist = headerList.get(i);

            for(int j = 0 ; j < columnlist.size() ; j ++ ){
                Map headerMap = columnlist.get(j);
                int colspan = SysNumber.toInt(headerMap.get("colspan"));
                int rowspan = SysNumber.toInt( headerMap.get("rowspan"));
                int startFlag = 0;
                for(int u = j ; u < arrayWidth; u++ ){
                    if(flag[i][u]){
                        startFlag = u;
                        break;
                    }
                }

                HSSFCell headerCell = null;
                if( colspan == 1 && rowspan == 1){
                    headerCell  = row.createCell(startFlag);
                }else{
                    CellRangeAddress headerCRA = new CellRangeAddress(rowNumber,rowNumber+rowspan-1,startFlag,startFlag+colspan - 1 );
                    sheet.addMergedRegion(headerCRA);
                    headerCell = row.createCell(startFlag);
                }

                headerCell.setCellValue((String) headerMap.get("title"));
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
                cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
                headerCell.setCellStyle(cellStyle);

                for(int x = i ; x < i+rowspan ; x ++ ){
                    for(int y = j ; y < j+colspan ; y ++ ){
                        int z = y;
                        while ( !flag[x][z] ){
                            z += 1;
                        }
                        flag[x][z] = false ;
                    }
                }
            }

            rowNumber++;
        }

//        for(List<Map> headerlist : headerList){
//            HSSFRow row = sheet.createRow(rowNumber);
//
//            int columnStart = 0 ;
//            for(Map headerMap : headerlist){
//                int colspan = (int) headerMap.get("colspan");
//                int rowspan = (int) headerMap.get("rowspan");
//                CellRangeAddress headerCRA = new CellRangeAddress(rowNumber,rowNumber+rowspan-1,columnStart,columnStart+colspan);
//                sheet.addMergedRegion(headerCRA);
//                HSSFCell headerCell  = row.createCell(columnStart);
//                headerCell.setCellValue((String) headerMap.get("title"));
//                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
//                cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
//                headerCell.setCellStyle(cellStyle);
//            }
//            rowNumber++;
//        }


//        /*创建表头栏*/
//        HSSFRow columnTitleRow = sheet.createRow(rowNumber);
//        for(int i = 0 ; i < fieldNameList.size() ; i ++ ){
//            HSSFCell columnTitleCell = columnTitleRow.createCell(i);
//            String textAlign = textAlignList.get(i);
//            if("left".equalsIgnoreCase(textAlign))
//                style.setAlignment(HorizontalAlignment.LEFT);
//            if("center".equalsIgnoreCase(textAlign))
//                style.setAlignment(HorizontalAlignment.CENTER);
//            if("right".equalsIgnoreCase(textAlign))
//                style.setAlignment(HorizontalAlignment.RIGHT);
//            columnTitleCell.setCellValue(fieldNameList.get(i));
//            columnTitleCell.setCellStyle(style);
//        }
//        rowNumber++;



        /*添加数据*/
        for(JSONObject row:rowsList){
            //获取数据
            HSSFRow dataRow = sheet.createRow(rowNumber);
            for(int i = 0 ; i < columnNumber ; i++){
                HSSFCell dataCell = dataRow.createCell(i);
                String dataValue = null;
                String field = fieldList.get(i);
                String value = row.getString(fieldList.get(i));
                if("state".endsWith(field) && value == "true"){
                    dataValue = "√";
                }else{
                    dataValue = row.getString(fieldList.get(i));
                }
                dataCell.setCellValue(dataValue);
                String textAlign = textAlignList.get(i);
                if("left".equalsIgnoreCase(textAlign))
                    cellStyle.setAlignment(HorizontalAlignment.LEFT);
                if("center".equalsIgnoreCase(textAlign))
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                if("right".equalsIgnoreCase(textAlign))
                    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                dataCell.setCellStyle(cellStyle);
            }
            rowNumber++;
        }

        /*添加footer*/
        HSSFRow footerRow = sheet.createRow(rowNumber);
        HSSFCell footerCell  = footerRow.createCell(0);
        footerCell.setCellValue(footer);
        /*合并*/
        CellRangeAddress footerCRA=new CellRangeAddress(rowNumber, rowNumber, 0,columnNumber-1);
        sheet.addMergedRegion(footerCRA);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        footerCell.setCellStyle(cellStyle);

        for(int i = 0 ; i < columnNumber ; i ++){
            int width;
            if(widthList.get(i) != null){
                width = (widthList.get(i)>255?250:widthList.get(i))/5;
            }else {
                width =20;
            }
            sheet.setColumnWidth(i,width*256+184);
        }
        return wb;
    }
}
